package com.movitech.mobile.cz.base.common.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;

import java.io.InputStream;
import java.util.*;

public class ExcelUtils {

    /***
     * 读取Excel测试，兼容 Excel 2003/2007/2010
     * @param inputStream
     * @return
     */
   public static List<List<Object>>  readBPExcel(InputStream inputStream)
   {
	   List<List<Object>>  list= null;
       try {
           Workbook workbook = WorkbookFactory.create(inputStream); 
           //Sheet的数量
           int sheetCount = workbook.getNumberOfSheets();
           //遍历每个Sheet
		   list = new ArrayList<List<Object>>();
           for (int s = 0; s < sheetCount; s++) {
               Sheet sheet = workbook.getSheetAt(s);
               if(sheet.getSheetName().equals("说明")){
               	continue;
			   }
             //获取总行数
               int rowCount = sheet.getPhysicalNumberOfRows(); 
               //遍历每一行
               for (int r = 0; r < rowCount; r++) {
                   //if(r==0) continue;
            	   Row row = sheet.getRow(r);

				   //遍历所有的列
				   Cell cell = null;
				   List<Object> li = new ArrayList<Object>();
				   for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
					   cell = row.getCell(y);
					   li.add(getCellValue(cell));
				   }
				   list.add(li);
               }
           }
       }
       catch (Exception e) {
           e.printStackTrace();
       }
       return list;
   }

	public static List<Object>  readHeaderExcel(InputStream inputStream)
	{
		List<Object>  list= null;
		try {
			Workbook workbook = WorkbookFactory.create(inputStream);
			int sheetCount = workbook.getNumberOfSheets();
			list = new ArrayList<Object>();
			for (int s = 0; s < sheetCount; s++) {
				Sheet sheet = workbook.getSheetAt(s);
				Row row = sheet.getRow(0);
				Cell cell = null;
				for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
					cell = row.getCell(y);
					list.add(getCellValue(cell));
				}
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
   


	/***
	 * 获取Excel表格消息第一条数据
	 * 
	 * @param inputStream
	 * @return
	 */
	public static Map<String,String> getMessageExcelFirstRow(InputStream inputStream) {
		Map<String,String> mapData = new HashMap<String,String>();
		try {
			Workbook workbook = WorkbookFactory.create(inputStream);
			// Sheet的数量
			int sheetCount = workbook.getNumberOfSheets();
			// 遍历每个Sheet
			for (int s = 0; s < sheetCount; s++) {
				Sheet sheet = workbook.getSheetAt(s);
				// 获取总行数
				int rowCount = sheet.getPhysicalNumberOfRows();
				// 遍历每一行
				for (int r = 0; r < rowCount; r++) {
					if (r == 0) continue;
					Row row = sheet.getRow(r);
					// 列数据
					String cnNumber = getCellValue(row.getCell(0));
					String content = getCellValue(row.getCell(1));
					mapData.put("CN_NUMBER", cnNumber);
					mapData.put("CONTENT", content);
					break;
				}
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return mapData;
	}
	
	/***
	 * 获取Excel表格消息数据
	 * 
	 * @param inputStream
	 * @return
	 */
	public static Map<String,Object> getMessageExcelData(InputStream inputStream) {
		Map<String,Object> mapData = new HashMap<String,Object>();
		boolean isError = false;
		try {
			List<Map<String,String>> lstExcelDataMap = new ArrayList<Map<String,String>>();
			Workbook workbook = WorkbookFactory.create(inputStream);
			// Sheet的数量
			int sheetCount = workbook.getNumberOfSheets();
			// 遍历每个Sheet
			for (int s = 0; s < sheetCount; s++) {
				Sheet sheet = workbook.getSheetAt(s);
				// 获取总行数
				int rowCount = sheet.getPhysicalNumberOfRows();
				// 遍历每一行
				for (int r = 0; r < rowCount; r++) {
					if (r == 0) continue;
					Row row = sheet.getRow(r);
					// 列数据
					Map<String,String> mapExcelRowData = new HashMap<String,String>();
					String cnNumber = getCellValue(row.getCell(0));
					String content = getCellValue(row.getCell(1));
					
					if(StringUtils.isNotEmpty(cnNumber) && StringUtils.isNotEmpty(content)){
						mapExcelRowData.put("CN_NUMBER", cnNumber);
						mapExcelRowData.put("CONTENT", content);
						lstExcelDataMap.add(mapExcelRowData);
					}else{
						isError = true;
						break;
					}
				}
				if(isError){
					break;
				}
			}
			mapData.put("IS_ERROR", isError);
			mapData.put("LIST_DATA", lstExcelDataMap);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return mapData;
	}

	public static String getCellValue(Cell cell) {
		String ret = "";
		if(cell!=null){
			switch (cell.getCellType()) {
				case Cell.CELL_TYPE_BLANK:
					ret = "";
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					ret = String.valueOf(cell.getBooleanCellValue());
					break;
				case Cell.CELL_TYPE_ERROR:
					ret = null;
					break;
				case Cell.CELL_TYPE_FORMULA:
					Workbook wb = cell.getSheet().getWorkbook();
					CreationHelper crateHelper = wb.getCreationHelper();
					FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
					ret = getCellValue(evaluator.evaluateInCell(cell));
					break;
				case Cell.CELL_TYPE_NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						Date theDate = cell.getDateCellValue();
						ret = DateUtils.format(theDate, DateUtils.DATE_TIME);
					} else {
						ret = NumberToTextConverter.toText(cell.getNumericCellValue());
					}
					break;
				case Cell.CELL_TYPE_STRING:
					ret = cell.getRichStringCellValue().getString();
					break;
				default:
					ret = null;
			}
		}

		return ret; // 有必要自行trim
	}
}
